﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using MINERP.DAL;
using System.Data;
using System.Data.SqlClient;

namespace MINERP.DAL.CustomerManage
{
    public class OrderProductDAL
    {
        //参数
        private newSampleYPDBEntities entity = new newSampleYPDBEntities();

        #region 获取产品的总量(在某种状态下)
        /// <summary>
        /// 获取产品的总量(在某种状态下)
        /// </summary>
        /// <param name="status"></param>
        /// <returns></returns>
        public int GetOrderInfoNum(int status)
        {
            try
            {
                int count = 0;
                if (status == -1)               //-1表示"全部"
                {
                    string esql = "select value count(it.OrderProductId) from orderInfo as it";
                    count = entity.CreateQuery<int>(esql).First();
                    return count;
                }
                else
                {
                    string esql = "select value count(it.OrderProductId) from orderInfo as it where it.status=" + status;
                    count = entity.CreateQuery<int>(esql).First();
                    return count;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取指定范围的产品
        /// <summary>
        /// 获取指定范围的产品
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public List<orderInfo> GetOrderProduct(int start, int end, int status)
        {
            try
            {
                if (status == -1)               //-1表示"全部"
                {
                    List<orderInfo> orderInfos = entity.orderInfo.OrderByDescending(p=>p.OrderProductCode).Skip(start - 1).Take(end - start + 1).ToList();
                    entity.Refresh(RefreshMode.StoreWins, orderInfos);
                    return orderInfos;
                }
                else
                {
                    List<orderInfo> orderInfos = entity.orderInfo.Where(p => p.status == status).OrderByDescending(p => p.OrderProductCode).Skip(start - 1).Take(end - start + 1).ToList();
                    entity.Refresh(RefreshMode.StoreWins, orderInfos);
                    return orderInfos;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion 



        #region 获取产品的当前流程及时间
        /// <summary>
        /// 获取产品的当前流程及时间
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public List<string> GetOrderProductCurrentTech(int orderProductId)
        {
            try
            {
                int status1 = (int)COM.OrderFlowListStatus.Useable;
                int status2 = (int)COM.OrderFlowListStatus.UseableAccept;
                string esql = "select it.AssignTime,it.tb_Tech.TechName from WorkOrderFlowList as it where it.orderInfo.OrderProductId=" + orderProductId + " and (it.status=" + status1 + " or it.status=" + status2 + ")";
                WorkOrderFlowList flow = entity.WorkOrderFlowList.Where(p => p.orderInfo.OrderProductId == orderProductId && (p.Status == status1 || p.Status == status2)).FirstOrDefault();
                List<string> techInfo=new List<string>();
                if (flow != null)
                {
                    if (!flow.tb_TechReference.IsLoaded)
                        flow.tb_TechReference.Load(MergeOption.OverwriteChanges);
                    techInfo.Add(flow.tb_Tech.TechName);
                    techInfo.Add(flow.AssignTime.ToString());
                }
                return techInfo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取产品的当前流程及时间(备份)
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public List<string> GetOrderProductCurrentTech_bk(int orderProductId)
        {
            try
            {
                int status1 = (int)COM.OrderFlowListStatus.Useable;
                int status2 = (int)COM.OrderFlowListStatus.UseableAccept;
                string esql = "select it.AssignTime,it.tb_Tech.TechName from WorkOrderFlowList_bk as it where it.orderInfo_bk.OrderProductId=" + orderProductId + " and (it.status=" + status1 + " or it.status=" + status2 + ")";
                WorkOrderFlowList_BK flow = entity.WorkOrderFlowList_BK.Where(p => p.orderInfo_BK.OrderProductId == orderProductId && (p.Status == status1 || p.Status == status2)).FirstOrDefault();
                List<string> techInfo = new List<string>();
                if (flow != null)
                {
                    if (!flow.tb_TechReference.IsLoaded)
                        flow.tb_TechReference.Load(MergeOption.OverwriteChanges);
                    techInfo.Add(flow.tb_Tech.TechName);
                    techInfo.Add(flow.AssignTime.ToString());
                }
                return techInfo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据产品自身条件获取产品
        /// <summary>
        /// 根据产品自身条件获取产品
        /// </summary>
        /// <param name="esql"></param>
        /// <returns></returns>
        public List<orderInfo> GetOrderInfoCondition(string esql)
        {
            try
            {
                string esql1 = "select value it from orderInfo as it " + esql;
                List<orderInfo> orderInfos = entity.CreateQuery<orderInfo>(esql1).ToList();
                return orderInfos;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据产品自身条件获取产品(存储过程)
        /// </summary>
        /// <param name="esql"></param>
        /// <returns></returns>
        public DataTable FindOrderInfo(string productCode,string ctmName,string shopName,DateTime start,DateTime end)
        {
            try
            {
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                DataTable dt = new DataTable();
                int techStatus1 = (int)COM.OrderFlowListStatus.Useable;
                int techStatus2 = (int)COM.OrderFlowListStatus.UseableAccept;
                int autoStartOrder = (int)COM.OrderBagStatus.AutoStartOrder;
                int delOrder = (int)COM.OrderBagStatus.Deleted;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    SqlCommand cmd = new SqlCommand("Proc_FindOrderInfo", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("techStatus1", techStatus1));
                    cmd.Parameters.Add(new SqlParameter("techStatus2", techStatus2));
                    cmd.Parameters.Add(new SqlParameter("autoStartOrder", autoStartOrder));
                    cmd.Parameters.Add(new SqlParameter("delOrderStatus", delOrder));
                    cmd.Parameters.Add(new SqlParameter("productCode", productCode));
                    cmd.Parameters.Add(new SqlParameter("ctmName", ctmName));
                    cmd.Parameters.Add(new SqlParameter("shopName", shopName));
                    cmd.Parameters.Add(new SqlParameter("start", start));
                    cmd.Parameters.Add(new SqlParameter("end", end));
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据产品自身条件获取产品(备份)
        /// </summary>
        /// <param name="esql"></param>
        /// <returns></returns>
        public List<orderInfo_BK> GetOrderInfoCondition_bk(string esql)
        {
            try
            {
                string esql1 = "select value it from orderInfo_bk as it " + esql;
                List<orderInfo_BK> orderInfos = entity.CreateQuery<orderInfo_BK>(esql1).ToList();
                return orderInfos;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据产品自身条件获取产品(存储过程 备份)
        /// </summary>
        /// <param name="esql"></param>
        /// <returns></returns>
        public DataTable FindOrderInfo_bk(string productCode, string ctmName, string shopName,DateTime start,DateTime end)
        {
            try
            {
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                DataTable dt = new DataTable();
                int techStatus1 = (int)COM.OrderFlowListStatus.Useable;
                int techStatus2 = (int)COM.OrderFlowListStatus.UseableAccept;
                int autoStartOrder = (int)COM.OrderBagStatus.AutoStartOrder;
                int delOrder = (int)COM.OrderBagStatus.Deleted;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    SqlCommand cmd = new SqlCommand("Proc_FindOrderInfo_bk", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("techStatus1", techStatus1));
                    cmd.Parameters.Add(new SqlParameter("techStatus2", techStatus2));
                    cmd.Parameters.Add(new SqlParameter("autoStartOrder", autoStartOrder));
                    cmd.Parameters.Add(new SqlParameter("delOrderStatus", delOrder));
                    cmd.Parameters.Add(new SqlParameter("productCode", productCode));
                    cmd.Parameters.Add(new SqlParameter("ctmName", ctmName));
                    cmd.Parameters.Add(new SqlParameter("shopName", shopName));
                    cmd.Parameters.Add(new SqlParameter("start", start));
                    cmd.Parameters.Add(new SqlParameter("end", end));
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region 根据照片编码查询产品
        /// <summary>
        /// 根据照片编码查询产品
        /// </summary>
        /// <param name="photoCode"></param>
        /// <returns></returns>
        public List<orderInfo> GetOrderInfoByPhotoCode(string photoCode)
        {
            try
            {
                List<photoInfo> photos = entity.photoInfo.Where("it.PhotoBarCode like '%" + photoCode + "%'").ToList();

                if (photos == null || photos.Count == 0)
                {
                    return null;
                }
                List<orderInfo> infos = new List<orderInfo>();
                foreach (photoInfo photo in photos)
                {
                    photo.orderInfoReference.Load();
                    infos.Add(photo.orderInfo);
                }
                return infos;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据照片编码查询产品(存储过程)
        /// </summary>
        /// <param name="photoCode"></param>
        /// <returns></returns>
        public DataTable FindOrderInfoByPhotoCode(string photoCode,DateTime start,DateTime end)
        {
            try
            {
                DataTable dt = new DataTable();
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                int techStatus1 = (int)COM.OrderFlowListStatus.Useable;
                int techStatus2 = (int)COM.OrderFlowListStatus.UseableAccept;
                int autoStartOrder = (int)COM.OrderBagStatus.AutoStartOrder;
                int delOrder = (int)COM.OrderBagStatus.Deleted;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    SqlCommand cmd = new SqlCommand("Proc_FindOrderInfoByPhotoCode", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("techStatus1", techStatus1));
                    cmd.Parameters.Add(new SqlParameter("techStatus2", techStatus2));
                    cmd.Parameters.Add(new SqlParameter("autoStartOrder", autoStartOrder));
                    cmd.Parameters.Add(new SqlParameter("delOrderStatus", delOrder));
                    cmd.Parameters.Add(new SqlParameter("photoCode", photoCode));
                    cmd.Parameters.Add(new SqlParameter("start", start));
                    cmd.Parameters.Add(new SqlParameter("end", end));
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据照片编码查询产品(备份)
        /// </summary>
        /// <param name="photoCode"></param>
        /// <returns></returns>
        public List<orderInfo_BK> GetOrderInfoByPhotoCode_bk(string photoCode)
        {
            try
            {
                List<photoInfo_BK> photos = entity.photoInfo_BK.Where("it.PhotoBarCode like '%" + photoCode + "%'").ToList();

                if (photos == null || photos.Count == 0)
                {
                    return null;
                }
                List<orderInfo_BK> infos = new List<orderInfo_BK>();
                foreach (photoInfo_BK photo in photos)
                {
                    photo.orderInfo_BKReference.Load();
                    infos.Add(photo.orderInfo_BK);
                }
                return infos;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据照片编码查询产品(存储过程 备份)
        /// </summary>
        /// <param name="photoCode"></param>
        /// <returns></returns>
        public DataTable FindOrderInfoByPhotoCode_bk(string photoCode, DateTime start, DateTime end)
        {
            try
            {
                DataTable dt = new DataTable();
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                int techStatus1 = (int)COM.OrderFlowListStatus.Useable;
                int techStatus2 = (int)COM.OrderFlowListStatus.UseableAccept;
                int autoStartOrder = (int)COM.OrderBagStatus.AutoStartOrder;
                int delOrder = (int)COM.OrderBagStatus.Deleted;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    SqlCommand cmd = new SqlCommand("Proc_FindOrderInfoByPhotoCode_bk", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("techStatus1", techStatus1));
                    cmd.Parameters.Add(new SqlParameter("techStatus2", techStatus2));
                    cmd.Parameters.Add(new SqlParameter("autoStartOrder", autoStartOrder));
                    cmd.Parameters.Add(new SqlParameter("delOrderStatus", delOrder));
                    cmd.Parameters.Add(new SqlParameter("photoCode", photoCode));
                    cmd.Parameters.Add(new SqlParameter("start", start));
                    cmd.Parameters.Add(new SqlParameter("end", end));
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 按时间范围获取产品
        /// <summary>
        /// 按时间范围获取产品
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public List<Tb_OrderBag> GetOrderProductByTime(DateTime start, DateTime end,int status)
        {
            try
            {
                    int delStatus = (int)COM.OrderBagStatus.Deleted;
                if (status == -1)
                {
                    //string esql = "select value it from orderInfo as it where it.Tb_OrderBag.OrderTime>" + start + " and it.Tb_OrderBag.OrderTime<" + end + " and it.Tb_OrderBag.Status!=" + delStatus;
                    //List<orderInfo> infos = entity.CreateQuery<orderInfo>(esql).ToList();
                    //return infos.OrderByDescending(p => p.OrderProductCode).ToList();
                    List<Tb_OrderBag> orders = entity.Tb_OrderBag.Where(p => p.Status != delStatus && p.OrderTime > start && p.OrderTime < end).ToList();
                    return orders;
                }
                else
                {
                    //string esql = "select value it from orderInfo as it where it.Tb_OrderBag.OrderTime>cast('" + start + "' as System.DateTime) and it.Tb_OrderBag.OrderTime<cast('" + end + "' as System.DateTime) and it.Tb_OrderBag.Status!=" + delStatus + " and it.status=" + status;
                    //List<orderInfo> infos = entity.CreateQuery<orderInfo>(esql).ToList();
                    //return infos.OrderByDescending(p => p.OrderProductCode).ToList();
                    List<Tb_OrderBag> orders = entity.Tb_OrderBag.Where(p => p.Status != delStatus && p.OrderTime > start && p.OrderTime < end).ToList();
                    return orders;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取产品列表
        /// <summary>
        /// 获取产品列表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public DataTable GetOrderInfoByTimeStoreProcedure(DateTime start, DateTime end, int status)
        {
            //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
            string conStr = DALBase.ConnStr;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                int flowStatus1 = (int)COM.OrderFlowListStatus.Useable;
                int flowStatus2 = (int)COM.OrderFlowListStatus.UseableAccept;
                int delOrderStatus = (int)COM.OrderBagStatus.Deleted;
                int autoStartOrder=(int)COM.OrderBagStatus.AutoStartOrder;
                SqlCommand cmd = new SqlCommand("Proc_GetOrderInfoList", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("startTime", start));
                cmd.Parameters.Add(new SqlParameter("endTime", end));
                cmd.Parameters.Add(new SqlParameter("infoStatus", status));
                cmd.Parameters.Add(new SqlParameter("techStatus1", flowStatus1));
                cmd.Parameters.Add(new SqlParameter("techStatus2", flowStatus2));
                cmd.Parameters.Add(new SqlParameter("delOrderStatus", delOrderStatus));
                cmd.Parameters.Add(new SqlParameter("autoStartOrder", autoStartOrder));     //自动开单，还未审核
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);   
                
                DataTable dt=new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 获取产品列表(备份)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public DataTable GetOrderInfoByTimeStoreProcedureBK(DateTime start, DateTime end, int status)
        {
            //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
            string conStr =DALBase.ConnStr;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                int flowStatus1 = (int)COM.OrderFlowListStatus.Useable;
                int flowStatus2 = (int)COM.OrderFlowListStatus.UseableAccept;
                int delOrderStatus = (int)COM.OrderBagStatus.Deleted;
                SqlCommand cmd = new SqlCommand("Proc_GetOrderInfoListBK", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("startTime", start));
                cmd.Parameters.Add(new SqlParameter("endTime", end));
                cmd.Parameters.Add(new SqlParameter("infoStatus", status));
                cmd.Parameters.Add(new SqlParameter("techStatus1", flowStatus1));
                cmd.Parameters.Add(new SqlParameter("techStatus2", flowStatus2));
                cmd.Parameters.Add(new SqlParameter("delOrderStatus", delOrderStatus));
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
                throw ex;
            }
        }
        #endregion

        #region 获取批量加急的产品
        /// <summary>
        /// 获取批量加急的产品
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="status"></param>
        /// <param name="priority"></param>
        /// <returns></returns>
        public DataTable GetOrderInfoByTimeStoreProcedure2(DateTime start, DateTime end, int status,int priority)
        {
            ////string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
            string conStr = DALBase.ConnStr;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                int flowStatus1 = (int)COM.OrderFlowListStatus.Useable;
                int flowStatus2 = (int)COM.OrderFlowListStatus.UseableAccept;
                int delOrderStatus = (int)COM.OrderBagStatus.Deleted;
                SqlCommand cmd = new SqlCommand("Proc_GetOrderInfoListPriority", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("startTime", start));
                cmd.Parameters.Add(new SqlParameter("endTime", end));
                cmd.Parameters.Add(new SqlParameter("infoStatus", status));
                cmd.Parameters.Add(new SqlParameter("techStatus1", flowStatus1));
                cmd.Parameters.Add(new SqlParameter("techStatus2", flowStatus2));
                cmd.Parameters.Add(new SqlParameter("delOrderStatus", delOrderStatus));
                cmd.Parameters.Add(new SqlParameter("priority", priority));
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                DataTable dt = new DataTable();
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
                throw ex;
            }
        }
        #endregion

        #region 加急
        /// <summary>
        /// 加急
        /// </summary>
        /// <param name="orderProductIds"></param>
        /// <param name="priority"></param>
        public void SetPriority(List<int> orderProductIds, int priority)
        {
            //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
            string conStr = DALBase.ConnStr;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql1 = "update orderinfo set priority=" + priority + " where orderProductid=";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                string sql2 = "";
                con.Open();
                foreach (int orderProductId in orderProductIds)
                {
                    sql2 = sql1 + orderProductId;
                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        //------------------新方法-----------------
        #region 根据产品Id取得订单Id
        /// <summary>
        /// 根据产品Id取得订单Id
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public int GetOrderBagIdByOrderProductId(int orderProductId)
        {
            try
            {
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = "select orderbagid from orderinfo where orderproductid=" + orderProductId;
                    SqlCommand cmd = new SqlCommand(sql, con);
                    con.Open();
                    int orderBagId = Convert.ToInt32(cmd.ExecuteScalar());
                    return orderBagId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 根据产品Id取得其所属订单的状态
        /// <summary>
        /// 根据产品Id取得其所属订单的状态
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public int GetOrderStatusByOrderProductId(int orderProductId)
        {
            try
            {
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = "select count(tb_orderbag.orderbagid) from tb_orderbag inner join orderinfo on tb_orderbag.orderbagid=orderinfo.orderbagid and orderproductid=" + orderProductId;
                    string sql2 = "select tb_orderbag.status from orderinfo inner join tb_orderbag on orderinfo.orderbagid=tb_orderbag.orderbagid and orderinfo.orderproductid=" + orderProductId;
                    string sql3 = "select tb_orderbag_bk.status from orderinfo_bk inner join tb_orderbag_bk on orderinfo_bk.orderbagid=tb_orderbag_bk.orderbagid and orderinfo_bk.orderproductid=" + orderProductId;
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    con.Open();
                    int count = Convert.ToInt16(cmd.ExecuteScalar());
                    int status = -1;
                    if (count > 0)                  //表示订单还未完成
                    {
                        cmd.CommandText = sql2;
                        status = Convert.ToInt16(cmd.ExecuteScalar());
                    }
                    else
                    {
                        cmd.CommandText = sql3;
                        status = Convert.ToInt16(cmd.ExecuteScalar());
                    }
                    return status;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region
        /// <summary>
        /// 产品内所有的照片
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public DataTable GetPhotosByOrderProductId(int orderProductId)
        {
            try
            {
                string conStr = DALBase.ConnStr;
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = "select PhotoId,PhotoName,PhotoBarCode,TotalNum as PhotoNum,printer.Pname as PrinterName,Height,Width,Paperface.Name as PaperFace,papertype.Name as PaperType,PicPath,photoinfo.Remark Remark from photoinfo";
                    sql += " inner join paperface on orderproductid="+orderProductId+" and photoinfo.paperfaceid=paperface.paperfaceid";
                    sql += " inner join papertype on photoinfo.papertypeid=papertype.papertypeid ";
                    sql += " left join printer on photoinfo.printerid=printer.printerid";
                    sql += " order by photoId";
                    SqlCommand cmd = new SqlCommand(sql, con);
                    DataTable dt = new DataTable();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetPhotosByOrderProductId_bk(int orderProductId)
        {
            try
            {
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = "select PhotoId,PhotoName,PhotoBarCode,TotalNum as PhotoNum,printer.Pname as PrinterName,Height,Width,Paperface.Name as PaperFace,papertype.Name as PaperType,PicPath,photoinfo_bk.Remark Remark from photoinfo_bk";
                    sql += " inner join paperface on orderproductid=" + orderProductId + " and photoinfo_bk.paperfaceid=paperface.paperfaceid";
                    sql += " inner join papertype on photoinfo_bk.papertypeid=papertype.papertypeid ";
                    sql += " left join printer on photoinfo_bk.printerid=printer.printerid";
                    sql += " order by photoId";
                    SqlCommand cmd = new SqlCommand(sql, con);
                    DataTable dt = new DataTable();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 产品流程
        /// <summary>
        /// 产品流程
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public DataTable GetFlowByOrderProductId(int orderProductId)
        {
            try
            {
                string conStr = DALBase.ConnStr;
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = "select WorkOrder,TechName,UserName,AssignTime,CompletedTime,Status,'' as StatusStr,PreCompleteTime,TechType,Workorderflowlist.userid from Workorderflowlist";
                    sql += " inner join tb_tech on orderproductid="+orderProductId+" and workorderflowlist.techid=tb_tech.techid";
                    sql += " left join users on workorderflowlist.userid=users.userid order by WorkOrder";
                    SqlCommand cmd = new SqlCommand(sql, con);
                    DataTable dt = new DataTable();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetFlowByOrderProductId_bk(int orderProductId)
        {
            try
            {
                //string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
                string conStr = DALBase.ConnStr;
                using (SqlConnection con = new SqlConnection(conStr))
                {
                    string sql = "select WorkOrder,TechName,UserName,AssignTime,CompletedTime,Status,'' as StatusStr,PreCompleteTime,TechType,Workorderflowlist.userid from Workorderflowlist_bk";
                    sql += " inner join tb_tech on orderproductid=" + orderProductId + " and workorderflowlist_bk.techid=tb_tech.techid";
                    sql += " left join users on workorderflowlist_bk.userid=users.userid order by WorkOrder";
                    SqlCommand cmd = new SqlCommand(sql, con);
                    DataTable dt = new DataTable();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 产品的停止，恢复记录
        /// <summary>
        /// 产品的停止，恢复记录
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public DataTable GetStopRecordByOrderProductId(int orderProductId)
        { 
           // string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["fffff"].ToString();
            string conStr = DALBase.ConnStr;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                string sql = "select CtmOperateRecord.CreateTime,CtmOperateRecord.operatetype,'' as OrderProductStatus,Remark,TechName,Username as WorkerName from CtmOperateRecord inner join tb_tech on CtmOperateRecord.currenttechid=tb_tech.techid and OrderProductId=" + orderProductId;
                sql += " left join users on ctmoperaterecord.operatorid=users.userid order by CtmOperateRecord.CreateTime";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
        }
        #endregion

        public int GetProductNum(string Sql)
        {

            string conStr = DALBase.ConnStr;
            using (SqlConnection con = new SqlConnection(conStr))
            {

                SqlCommand cmd = new SqlCommand(Sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                
                return Convert.ToInt32(dt.Rows[0][0]);
            }
        }
    }
}
